#!/usr/bin/env python
# coding=utf-8
# CopyRight(c) 2019 Inspur Electronic Information Industry Co.,Ltd

"""
    secchecksuite/sqlitedb.py
    sqlitedb.py for database management
    :CopyRight(c) 2019 Inspur Electronic Information Industry Co.,Ltd
"""

import os
import sys
import datetime
import sqlite3
import constant as const
import utils
import seccheck_log
import aes_cbc
import check


def connect_sqlite(db_path):
    """
    connect sqlite3 datebase
    Args:
        db_path: sqlite database path
    Returns:
        conn:  database connect object
    """
    conn = sqlite3.connect(db_path)
    if os.path.exists(db_path) and os.path.isfile(db_path):
        return conn
    else:
        # conn = None
        return sqlite3.connect(':memory:')


def create_table_sqlite(conn, table_info):
    """
    create table in sqlite database
    Args:
        conn: sqlite database connect object
        table_info: info to create tables
    Returns:
        None
    Raises:
        BaseException:  An error occurred creating a table
    """
    try:
        if table_info is not None and table_info != '':
            cursor = conn.cursor()
            sql = table_info
            cursor.execute(sql)
            conn.commit()
            seccheck_log.sec_check_logger.info('create_table_sqlite the params not None!')
        else:
            seccheck_log.sec_check_logger.info('create_table_sqlite the params is empty or equal None!')
    except BaseException as e:
        seccheck_log.sec_check_logger.error('create_table_sqlite occur some errors: {}'.format(e))


def drop_table_sqlite(conn, table):
    """
    drop table in sqlite database
    Args:
        conn: sqlite database connect object
        table: table will be dropped
    Returns:
        None
    Raises:
        BaseException:  An error occurred dropping
    """
    try:
        if table is not None and table != '':
            dbinfo = 'DROP TABLE IF EXISTS {}'.format(table)
            cursor = conn.cursor()
            cursor.execute(dbinfo)
            conn.commit()
            des = 'drop table [{}] success'.format(table)
            seccheck_log.sec_check_logger.info(des)
        else:
            des = 'drop_table_sqlite the params is empty or equal None!'
            seccheck_log.sec_check_logger.info(des)
    except BaseException as e:
        des = 'drop_table_sqlite occur error:{}'.format(e)
        seccheck_log.sec_check_logger.error(des)


def insert_info_sqlite(conn, dbinfo, insert_data):
    """
    insert some info to table in sqlite database
    Args:
        conn: sqlite database connect object
        dbinfo: insert info
        insert_data: insert data to sqlite database
    Returns:
        None
    Raises:
        BaseException:  An error occurred dropping
    """
    try:
        if dbinfo is not None and dbinfo != '':
            conn.text_factory = str
            cursor = conn.cursor()
            sql = dbinfo
            cursor.execute(sql, insert_data)
            conn.commit()
            seccheck_log.sec_check_logger.info('insert info sqlite succeeded')
        else:
            seccheck_log.sec_check_logger.info('insert_info_sqlite the params is empty or equal None!')
    except BaseException as e:
        seccheck_log.sec_check_logger.error('insert_info_sqlite occur error'.format(e))


def delete_info_sqlite(conn, dbinfo):
    """
    delete some info to table in sqlite database
    Args:
        conn: sqlite database connect object
        dbinfo: delete info
    Returns:
        None
    Raises:
        BaseException:  An error occurred dropping
    """
    try:
        if dbinfo is not None and dbinfo != '':
            cursor = conn.cursor()
            sql = dbinfo
            cursor.execute(sql)
            conn.commit()
            seccheck_log.sec_check_logger.info('delete info from database succeeded')
        else:
            seccheck_log.sec_check_logger.info('delete_info_sqlite the params is empty or equal None!')
    except BaseException as e:
        seccheck_log.sec_check_logger.error('delete_info_sqlite occur error'.format(e))


def fetchall_info_sqlite(conn, dbinfo, query_data):
    """
    query many info from table in sqlite database
    Args:
        conn: sqlite database connect object
        dbinfo: select info
        query_data: query query_data from table
    Returns:
        result info list
    Raises:
        BaseException:  An error occurred dropping
    """
    rows = []
    try:
        if dbinfo is not None and dbinfo != '':
            conn.row_factory = sqlite3.Row
            cursor = conn.cursor()
            sql = dbinfo
            cursor.execute(sql, query_data)
            rows = cursor.fetchall()
            seccheck_log.sec_check_logger.info('query plugins info from database succeeded')
        else:
            seccheck_log.sec_check_logger.info('fetchall_info_sqlite the params is empty or equal None!')
    except BaseException as e:
        seccheck_log.sec_check_logger.error('there is fetchall_info_sqlite occur some error {}'.format(e))
    return rows


def check_index_exist(conn, dbinfo, data):
    """
    check index exist
    Args:
        conn: sqlite database connect object
        dbinfo: select info
        data: bind data
    Returns:
        true/false
    Raises:
        BaseException: sql error
    """
    try:
        if dbinfo is not None and dbinfo != '':
            if data is not None and data != '':
                cursor = conn.cursor()
                cursor.execute(dbinfo, data)
                result = cursor.fetchone()
                return result
    except BaseException as e:
        seccheck_log.sec_check_logger.error('check_index_exist occur error'.format(e))
    return False


def create_index(conn, index_sql):
    """
    create index
    Args:
        conn: sqlite database connect object
        index_sql: create index sql
    Returns:
        None
    Raises:
        BaseException: sql error
    """
    try:
        if index_sql is not None and index_sql != '':
            cursor = conn.cursor()
            cursor.execute(index_sql)
            conn.commit()
            seccheck_log.sec_check_logger.info('create index sqlite succeeded')
        else:
            seccheck_log.sec_check_logger.info('create_index the params is empty or equal None!')
    except BaseException as e:
        seccheck_log.sec_check_logger.error('create_index occur error'.format(e))


def fetchone_info_sqlite(conn, dbinfo, query_data):
    """
    query a info from table in sqlite database
    Args:
        conn: sqlite database connect object
        dbinfo: select info
        query_data: query a query_data info from sqlite database
    Returns:
        result info
    Raises:
        BaseException:  An error occurred dropping
    """
    try:
        r = ""
        if dbinfo is not None and dbinfo != '':
            cursor = conn.cursor()
            sql = dbinfo
            cursor.execute(sql, query_data)
            r = cursor.fetchone()
        else:
            seccheck_log.sec_check_logger.info('fetchall_info_sqlite the params is empty or equal None!')
        return r
    except Exception as e:
        des = 'fetchone_info_sqlite there is occur some error {}'.format(e)
        print(des)
        seccheck_log.sec_check_logger.error(des)
        return None


def update_info_sqlite(conn, dbinfo, update_data):
    """
    update a info from table in sqlite database
    Args:
        conn: sqlite database connect object
        dbinfo: select info
        update_data: update update_data from sqlite database
    Returns:
        None
    Raises:
        BaseException:  An error occurred dropping
    """
    try:
        if dbinfo is not None and dbinfo != '':
            conn.text_factory = str
            cursor = conn.cursor()
            cursor.execute(dbinfo, update_data)
            conn.commit()
            seccheck_log.sec_check_logger.info('update_info_sqlite succeeded')
        else:
            seccheck_log.sec_check_logger.info('update_info_sqlite the params is empty or equal None!')
    except Exception as e:
        print('update_info_sqlite,there is occur some error {}', e)
        seccheck_log.sec_check_logger.error('update_info_sqlite,there is occur some error {}'.format(e))


def close_sqlite(conn):
    """
    close the connect to sqlite database
    Args:
        conn: sqlite database connect object
    Returns:
        None
    Raises:
        BaseException:  An error occurred dropping
    """
    try:
        if conn is not None:
            conn.close()
            seccheck_log.sec_check_logger.info('close sqlite database succeeded')
    except BaseException as e:
        print('close sqlite database occur some errors:{}'.format(e))
        seccheck_log.sec_check_logger.error('close sqlite database occur some errors:{}'.format(e))


def insert_into_database(plugins_list, conn, key):
    """
    insert plugin info to database
    Args:
        plugins_list: plugins list
        conn: sqlitedb connection
        key: encryt or decrpt key
    Returns:
        plugins_list: plugin info
    Raises:
        BaseException:  An error occurred creating a table
    """
    if not check.check_not_none_dict(plugins_list):
        return
    try:
        enter_point_name = plugins_list.get('enter_point_name', None)
        seccheck_log.sec_check_logger.info('insert plugin: {} info into database begin'.format(enter_point_name))
        select_sql = '''select hashvalue from T_PLUGIN_INFO where entrypoint = ? and plugin_id = ?
                    and version = ? '''
        fetch_list = [plugins_list.get('enter_point_name', None),
                      plugins_list.get('plugin_id', None),
                      plugins_list.get('version', None)]
        value = fetchone_info_sqlite(conn, select_sql, fetch_list)
        if value is None or value == '':
            insert_plugin_info_database(plugins_list, conn)
        else:
            hashvalue = aes_cbc.aes_decrypt(key, value[0], out=False)
            if hashvalue == plugins_list.get('hashvalue', None):
                update_plugin_info_database(plugins_list, conn)
            else:
                time = plugins_list.get('timenow', None)
                update_sql = '''UPDATE T_PLUGIN_INFO SET endtime = ? WHERE entrypoint = ? 
                         and plugin_id = ? and version = ? '''
                update_list = [time,
                               plugins_list.get('enter_point_name', None),
                               plugins_list.get('plugin_id', None),
                               plugins_list.get('version', None)]
                update_info_sqlite(conn, update_sql, update_list)
        seccheck_log.sec_check_logger.info('insert plugin {} info into database succeeded'.format(enter_point_name))
    except BaseException as e:
        seccheck_log.sec_check_logger.error('some error occur: {}'.format(e))


def insert_plugin_info_database(plugins_list, conn):
    """
    insert plugininfo to database
    Args:
        plugins_list: plugins list
        conn: sqlitedb connection
    Raises:
        BaseException:  An error occurred creating a table
    """
    if not check.check_not_none_dict(plugins_list):
        return
    insert_sql = '''insert into T_PLUGIN_INFO(oid, entrypoint, plugin_id,
                plugin_name, plugin_exe, plugin_config, user_info,
                encryption, plugin_level, plugin_timeout,
                hashvalue, version, plugintype, 
                scene, source, rollbackcmd, 
                createtime, endtime, stop_flag)
                values(NULL, ?, ?, ?,
                ?, ?, ?,
                ?, ?, ?,
                ?, ?, ?,
                ?, ?, ?,
                ?, ?, ?)
                '''
    insert_list = [plugins_list.get('enter_point_name', None),
                   plugins_list.get('plugin_id', None),
                   plugins_list.get('plugin_name', None),
                   plugins_list.get('plugin_exe', None),
                   plugins_list.get('plugin_config', None),
                   plugins_list.get('user_info', None),
                   plugins_list.get('encryption', None),
                   plugins_list.get('plugin_level', None),
                   plugins_list.get('plugin_timeout', None),
                   plugins_list.get('hashvalue_encrypt', None),
                   plugins_list.get('version', None),
                   plugins_list.get('plugintype', None),
                   plugins_list.get('scene', None),
                   plugins_list.get('source', None),
                   plugins_list.get('rollbackcmd', None),
                   plugins_list.get('timenow', None),
                   plugins_list.get('endtime', None),
                   plugins_list.get('stop_flag', None)]

    insert_info_sqlite(conn, insert_sql, insert_list)


def update_plugin_info_database(plugins_list, conn):
    """
    insert plugininfo to database
    Args:
        plugins_list: plugins list
        conn: sqlitedb connection
    Raises:
        BaseException:  An error occurred creating a table
    """
    if not check.check_not_none_dict(plugins_list):
        return

    update_sql = '''UPDATE T_PLUGIN_INFO SET plugin_name=?, plugin_exe=?, plugin_config=?, user_info=?,
                encryption=?, plugin_level=?, plugin_timeout=?,
                hashvalue=?,  plugintype=?, scene=?, source=?, 
                rollbackcmd=?, createtime=?, endtime=?, stop_flag=?
                where entrypoint=? and plugin_id=? and version=?'''
    param_list = [plugins_list.get('plugin_name', None),
                  plugins_list.get('plugin_exe', None),
                  plugins_list.get('plugin_config', None),
                  plugins_list.get('user_info', None),
                  plugins_list.get('encryption', None),
                  plugins_list.get('plugin_level', None),
                  plugins_list.get('plugin_timeout', None),
                  plugins_list.get('hashvalue_encrypt', None),
                  plugins_list.get('plugintype', None),
                  plugins_list.get('scene', None),
                  plugins_list.get('source', None),
                  plugins_list.get('rollbackcmd', None),
                  plugins_list.get('timenow', None),
                  plugins_list.get('endtime', None),
                  plugins_list.get('stop_flag', None),
                  plugins_list.get('enter_point_name', None),
                  plugins_list.get('plugin_id', None),
                  plugins_list.get('version', None)]
    update_info_sqlite(conn, update_sql, param_list)


def delete_sqlite_database(conn, config_info, database_name):
    """
    delete sqlite database
    Args:
        conn: sqlite database connection
        config_info: config info
        database_name: sqlite database name
    Returns:
        None
    """
    path = get_database_path(config_info, database_name)
    try:
        if os.path.exists(path):
            conn.close()
            os.remove(path)
    except BaseException as err:
        seccheck_log.sec_check_logger.error('remove database {} occur some errors: {}'.format(path, err))
        raise ('remove database {} occur some errors: {}'.format(path, err))


def get_database_path(config_info, database_name):
    """
    get database path
    Args:
        config_info: config info
        database_name: sqlite database name
    Returns:
        path: database path
    """
    db_file_config = config_info.get('dbfilepath', None)
    if not db_file_config:
        path = database_name
    else:
        path = os.path.join(db_file_config, database_name)
    return path


def getinfo_from_database(conn):
    """
    getinfo_from_database
    Args:
        conn: sqlite database connect object
    Returns:
        plugin_lists: plugins list
    Raises:
        BaseException:  An error occurred cleanup a table
    """
    # +1 second for compare between currentime and database
    try:
        seccheck_log.sec_check_logger.info('get plugins info from database begain')
        time_now = datetime.datetime.now()
        current_time = time_now + datetime.timedelta(seconds=1)
        sql = '''select *, max(version) from T_PLUGIN_INFO 
               where endtime > ? group by entrypoint, plugin_id order by stop_flag desc '''
        query_data = [current_time]
        plugin_lists = fetchall_info_sqlite(conn, sql, query_data)
        seccheck_log.sec_check_logger.info('get plugins info from database succeeded')
        return plugin_lists
    except BaseException as e:
        seccheck_log.sec_check_logger.error('getinfo_from_database occur errors:{}'.format(e))
        return None


def clean_database(conn, config_info):
    """
    clean sqlite database
    Args:
        conn: sqlite database connect object
        config_info: plugin config information
    Raises:
        BaseException:  An error occurred cleanup a table
    """
    try:
        seccheck_log.sec_check_logger.info('Clean sqlite database begin!')
        drop_table_sqlite(conn, 'T_PLUGIN_INFO')
        delete_sqlite_database(conn, config_info, const.SQLITE_DB_NAME)
        print('Clean up database succeeded')
        seccheck_log.sec_check_logger.info('Clean up database succeeded')
    except BaseException as e:
        seccheck_log.sec_check_logger.error('error:clean up failed'.format(e))
    finally:
        utils.program_exit(sys.argv[0], 0)


def set_sqlite_table_info(conn):
    """
    set sqlite information to create database
    Args:
        conn: sqlite database connect object
    Raises:
        BaseException:  An error occurred cleanup a table
    """
    # 创建表
    create_table = '''create table if not exists T_PLUGIN_INFO (
                            [oid]             integer PRIMARY KEY autoincrement,
                            [entrypoint]      text,
                            [plugin_id]       text,
                            [plugin_name]     text,
                            [plugin_exe]      text,
                            [plugin_config]   text,
                            [user_info]       text,
                            [encryption]      int default 0,
                            [plugin_level]    text default 'C',
                            [plugin_timeout]  int default 3,
                            [hashvalue]       text,
                            [version]         text,
                            [plugintype]      text,
                            [scene]           text,
                            [source]          text,
                            [stop_flag]       text,
                            [rollbackcmd]     text,
                            [createtime]      datetime default (datetime('now', 'localtime')),
                            [endtime]         datetime
                            );
                            '''
    create_table_sqlite(conn, create_table)
    index_sql = '''select * from sqlite_master where type = ? and name = ?'''
    data = ['index', 'plugin_info_index']
    index_is_exist = check_index_exist(conn, index_sql, data)
    if not index_is_exist:
        index_sql = '''create UNIQUE INDEX plugin_info_index on T_PLUGIN_INFO(entrypoint, plugin_id, version)'''
        create_index(conn, index_sql)
    seccheck_log.sec_check_logger.info('Init sqlite database succeeded!')
